In [1]:
import os
import pandas as pd
import matplotlib.pyplot as plt
from mpl_toolkits.basemap import Basemap
import matplotlib.colors as co
%matplotlib inline

define path

In [2]:
data_path = "./data"
countries_path = './data/countries.xlsx'
pdf_analysis_path = './data/total'
path_stock_list = "./data/stock.xlsx"

define function

In [3]:
def read_stock_list(path):
    df = pd.read_excel(path)
    return df

def get_total_stock_analysis(stock_list, path):
    total = pd.DataFrame()
    for index, row in stock_list.iterrows():
        try:
            stock_code = row['Stock Code']
            pkl_path = os.path.join(path, "%s_analysis.pkl" % (stock_code))
            df = pd.read_pickle(pkl_path)
            total = pd.concat([total, df])

        except Exception as e:
            print(e)

    return total
In [4]:
stock_list = read_stock_list(path_stock_list)
total_stock_analysis = get_total_stock_analysis(stock_list, pdf_analysis_path)
In [5]:
total_stock_analysis
Out[5]:
Country Count Stock Code Year Month lat lng Percent
0 Kosovo 1 5 2018 08 42.540622 20.956989 0.364964
1 Svalbard 0 5 2018 08 78.216700 15.633300 0.000000
2 Yemen 1 5 2018 08 14.673146 45.918938 0.364964
3 Mayotte 0 5 2018 08 -12.787100 45.275000 0.000000
4 South Africa 1 5 2018 08 -29.361468 25.863194 0.364964
5 Zambia 0 5 2018 08 -13.461203 27.676562 0.000000
6 Zimbabwe 0 5 2018 08 -18.929125 29.789730 0.000000
7 Kazakhstan 4 5 2018 08 48.332630 68.394287 1.459854
8 Laos 0 5 2018 08 18.246253 103.702632 0.000000
9 Lebanon 0 5 2018 08 33.933788 35.741725 0.000000
10 Saint Lucia 1 5 2018 08 14.002000 -61.000000 0.364964
11 Liechtenstein 0 5 2018 08 47.173055 9.526118 0.000000
12 Sri Lanka 0 5 2018 08 7.520893 80.437407 0.000000
13 Liberia 0 5 2018 08 6.204418 -9.585853 0.000000
14 Lesotho 0 5 2018 08 -29.541270 28.036410 0.000000
15 Lithuania 0 5 2018 08 55.310035 23.706761 0.000000
16 Luxembourg 1 5 2018 08 49.763292 6.129967 0.364964
17 Latvia 3 5 2018 08 56.885389 24.763713 1.094891
18 Libya 1 5 2018 08 29.876595 16.552838 0.364964
19 Morocco 0 5 2018 08 32.933276 -6.937876 0.000000
20 Monaco 1 5 2018 08 43.739600 7.406900 0.364964
21 Moldova 0 5 2018 08 47.238876 28.455026 0.000000
22 Montenegro 1 5 2018 08 42.683691 19.314991 0.364964
23 Saint Martin 0 5 2018 08 18.070600 -63.084700 0.000000
24 Madagascar 0 5 2018 08 -18.730979 46.919575 0.000000
25 Marshall Islands 0 5 2018 08 7.103000 171.380000 0.000000
26 Macedonia 2 5 2018 08 41.662755 21.727686 0.729927
27 Mali 2 5 2018 08 14.962368 -5.473658 0.729927
28 Burma 1 5 2018 08 18.916935 95.960573 0.364964
29 Mongolia 0 5 2018 08 47.630165 103.623296 0.000000
... ... ... ... ... ... ... ... ...
2310 Croatia 1 836 2017 07 45.185914 16.397933 0.374532
2311 Haiti 0 836 2017 07 19.000140 -72.740370 0.000000
2312 Hungary 1 836 2017 07 47.131216 18.987576 0.374532
2313 Indonesia 2 836 2017 07 -3.601422 113.473064 0.749064
2314 Ireland 1 836 2017 07 53.259888 -7.589185 0.374532
2315 Israel 0 836 2017 07 32.091583 34.991467 0.000000
2316 Isle Of Man 0 836 2017 07 54.150400 -4.480000 0.000000
2317 India 4 836 2017 07 21.680077 79.274420 1.498127
2318 Iraq 0 836 2017 07 33.490067 44.549963 0.000000
2319 Iran 0 836 2017 07 33.968683 51.980003 0.000000
2320 Iceland 0 836 2017 07 64.856167 -19.763744 0.000000
2321 Italy 1 836 2017 07 42.367664 12.728870 0.374532
2322 Jersey 0 836 2017 07 49.183300 -2.100000 0.000000
2323 Jamaica 0 836 2017 07 18.154573 -77.201347 0.000000
2324 Jordan 0 836 2017 07 31.580000 35.784017 0.000000
2325 Japan 0 836 2017 07 36.392775 136.926279 0.000000
2326 Kenya 1 836 2017 07 -0.569373 36.890329 0.374532
2327 Kyrgyzstan 1 836 2017 07 41.803413 74.294727 0.374532
2328 Cambodia 0 836 2017 07 12.299707 104.779367 0.000000
2329 Kiribati 0 836 2017 07 1.338200 173.017600 0.000000
2330 Comoros 0 836 2017 07 -11.704200 43.240200 0.000000
2331 Saint Kitts And Nevis 0 836 2017 07 17.302000 -62.717000 0.000000
2332 Korea, North 1 836 2017 07 39.966486 126.951783 0.374532
2333 Korea, South 1 836 2017 07 36.376890 127.602494 0.374532
2334 Kuwait 0 836 2017 07 29.261367 47.906733 0.000000
2335 Cayman Islands 0 836 2017 07 19.280400 -81.330000 0.000000
2336 American Samoa 0 836 2017 07 -14.274000 -170.704600 0.000000
2337 Guam 0 836 2017 07 13.471850 144.741800 0.000000
2338 Northern Mariana Islands 0 836 2017 07 15.213700 145.754600 0.000000
2339 United States 73 836 2017 07 38.252438 -92.039345 27.340824

99684 rows × 8 columns

Calculate Percent mean in Group by Stock Code, Country

In [6]:
country_stock_mean = total_stock_analysis.groupby(['Country', "lat", "lng", "Stock Code"])['Percent'].mean().reset_index()
In [7]:
country_stock_mean
Out[7]:
Country lat lng Stock Code Percent
0 Afghanistan 34.609883 67.409593 1 0.034928
1 Afghanistan 34.609883 67.409593 2 0.000000
2 Afghanistan 34.609883 67.409593 3 0.000000
3 Afghanistan 34.609883 67.409593 5 0.000000
4 Afghanistan 34.609883 67.409593 11 0.000000
5 Afghanistan 34.609883 67.409593 12 0.000000
6 Afghanistan 34.609883 67.409593 16 0.000000
7 Afghanistan 34.609883 67.409593 17 0.000000
8 Afghanistan 34.609883 67.409593 19 0.000000
9 Afghanistan 34.609883 67.409593 27 0.000000
10 Afghanistan 34.609883 67.409593 66 0.000000
11 Afghanistan 34.609883 67.409593 83 0.000000
12 Afghanistan 34.609883 67.409593 101 0.000000
13 Afghanistan 34.609883 67.409593 151 0.000000
14 Afghanistan 34.609883 67.409593 175 0.000000
15 Afghanistan 34.609883 67.409593 267 0.000000
16 Afghanistan 34.609883 67.409593 288 0.000000
17 Afghanistan 34.609883 67.409593 386 0.000000
18 Afghanistan 34.609883 67.409593 388 0.000000
19 Afghanistan 34.609883 67.409593 688 0.000000
20 Afghanistan 34.609883 67.409593 700 0.000000
21 Afghanistan 34.609883 67.409593 762 0.000000
22 Afghanistan 34.609883 67.409593 823 0.000000
23 Afghanistan 34.609883 67.409593 836 0.000000
24 Afghanistan 34.609883 67.409593 857 0.000000
25 Afghanistan 34.609883 67.409593 883 0.000000
26 Afghanistan 34.609883 67.409593 939 0.000000
27 Afghanistan 34.609883 67.409593 941 0.000000
28 Afghanistan 34.609883 67.409593 1038 0.000000
29 Afghanistan 34.609883 67.409593 1044 0.000000
... ... ... ... ... ...
11436 Zimbabwe -18.929125 29.789730 688 0.000000
11437 Zimbabwe -18.929125 29.789730 700 0.000000
11438 Zimbabwe -18.929125 29.789730 762 0.000000
11439 Zimbabwe -18.929125 29.789730 823 0.000000
11440 Zimbabwe -18.929125 29.789730 836 0.000000
11441 Zimbabwe -18.929125 29.789730 857 0.000000
11442 Zimbabwe -18.929125 29.789730 883 0.000000
11443 Zimbabwe -18.929125 29.789730 939 0.000000
11444 Zimbabwe -18.929125 29.789730 941 0.000000
11445 Zimbabwe -18.929125 29.789730 1038 0.000000
11446 Zimbabwe -18.929125 29.789730 1044 0.000000
11447 Zimbabwe -18.929125 29.789730 1088 0.000000
11448 Zimbabwe -18.929125 29.789730 1093 0.000000
11449 Zimbabwe -18.929125 29.789730 1109 0.000000
11450 Zimbabwe -18.929125 29.789730 1113 0.000000
11451 Zimbabwe -18.929125 29.789730 1177 0.000000
11452 Zimbabwe -18.929125 29.789730 1299 0.000000
11453 Zimbabwe -18.929125 29.789730 1398 0.000000
11454 Zimbabwe -18.929125 29.789730 1928 0.000000
11455 Zimbabwe -18.929125 29.789730 1997 0.000000
11456 Zimbabwe -18.929125 29.789730 2007 0.000000
11457 Zimbabwe -18.929125 29.789730 2018 0.000000
11458 Zimbabwe -18.929125 29.789730 2313 0.000000
11459 Zimbabwe -18.929125 29.789730 2318 0.000000
11460 Zimbabwe -18.929125 29.789730 2319 0.000000
11461 Zimbabwe -18.929125 29.789730 2382 0.000000
11462 Zimbabwe -18.929125 29.789730 2388 0.000000
11463 Zimbabwe -18.929125 29.789730 2628 0.000000
11464 Zimbabwe -18.929125 29.789730 3328 0.000000
11465 Zimbabwe -18.929125 29.789730 3988 0.000000

11466 rows × 5 columns

define function to draw map

In [12]:
def plot_maps(frame, stock_list):
    for index, row in stock_list.iterrows():
        try:
            stock_code = row['Stock Code']
            m = Basemap(projection='cyl')
            scale = 0.2
            m.shadedrelief(scale=scale)

            cmap = plt.cm.jet
            vmin = 0
            vmax = 30

            data = frame.loc[frame['Stock Code'] == stock_code]

            lon = [row['lng'] for index, row in data.iterrows()]
            lat = [row['lat'] for index, row in data.iterrows()]
            wind = [row['Percent'] for index, row in data.iterrows()]
            plt.scatter(lon, lat, marker='o',
                        c=wind, alpha=0.5, zorder=10, vmin=vmin, vmax=vmax, cmap=cmap)

            try:
                norm = co.Normalize(vmin=vmin, vmax=vmax)
                pointcolors = plt.cm.ScalarMappable(norm, cmap)
                pointcolors.set_array([])
                cbar = m.colorbar(pointcolors, location='bottom')
                cbar.set_label('%')
            except Exception as e:
                pass

            plt.title('Stock %s Country distribution' % stock_code)
            plt.show()


        except Exception as e:
            print(e)
In [13]:
plot_maps(country_stock_mean, stock_list)
In [ ]: